/***********************************************************************************************************************************
 * Name:	CMD_BPC-In.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Generate BCP IN commands for all tables in the current database.
**/

SELECT cmd= 'BCP "' + DB_NAME() + '.' + SCHEMA_NAME(schema_id) + '.' +  name + '" IN "' + SCHEMA_NAME(schema_id) + N'-' + name + '.dat" -E -k -q -T -f "'  + SCHEMA_NAME(schema_id) + N'-' + name + '.xml" ' + '-S ' + @@SERVERNAME
  FROM sys.tables WHERE type = 'U' AND is_ms_shipped = 0ORDER BY SCHEMA_NAME(schema_id), name;
GO

DECLARE @folder NVARCHAR(128)= master.DBAdmin.GetInstancePath('SQLDataRoot')+'BCP\';
SELECT cmd= N'BULK INSERT [' + SCHEMA_NAME(schema_id) + N'].[' + name + N'] FROM N''' + @folder + SCHEMA_NAME(schema_id) + N'-' + name + N'.dat'' WITH (FORMATFILE= N'''+ @folder + SCHEMA_NAME(schema_id) + N'-' + name + N'.xml'', KEEPIDENTITY, KEEPNULLS);'
  FROM sys.tables WHERE type = 'U' AND is_ms_shipped = 0 ORDER BY SCHEMA_NAME(schema_id), name;
GO